﻿using Microsoft.Extensions.Configuration;
using MySql.Data.MySqlClient;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace LPBToolsLib
{
    /*
    AppSetting.json格式
    
	"MySQLCFG": {
		"Address": "127.0.0.1",
		"DBUser": "BSDUser",
		"DBPass": "BSDUser",
		"DBName": "BiaoSiDist",
		"DBPort": "3306"
	},
    */

    public class MySQLConfig :  IFSQLConfig
    {
        private JSONINISettings BaseSetCFG { get; set; } = null;
        public string Segment { get; private set; } = "";
        public MySQLConfig(JSONINISettings tBaseSetCFG, string DestSegment = "") 
        {
            BaseSetCFG = tBaseSetCFG;
            if (string.IsNullOrEmpty(DestSegment))
                Segment = "MySQL";
            else
                Segment = DestSegment;
        }

        [DisplayName("服务地址")]
        public string Address
        {
            get
            {
                if (BaseSetCFG != null)
                    return BaseSetCFG.getCFGValue<string>(Segment, "Address", "127.0.0.1");
                return "127.0.0.1";
            }
            set { BaseSetCFG?.setCFGValue(Segment, "Address", value); }
        }


        [DisplayName("登陆用户")]
        public string DBUser
        {
            get
            {
                if (BaseSetCFG != null)
                    return BaseSetCFG.getCFGValue<string>(Segment, "DBUser", "");
                return "";
            }
            set { BaseSetCFG?.setCFGValue(Segment, "DBUser", value); }
        }

        [DisplayName("登陆密码")]
        public string DBPass
        {
            get
            {
                if (BaseSetCFG != null)
                    return BaseSetCFG.getCFGValue<string>(Segment, "DBPass", "");
                return "";
            }
            set { BaseSetCFG?.setCFGValue(Segment, "DBPass", value); }
        }

        [DisplayName("连接初始数据库名称")]
        public string DBName
        {
            get
            {
                if (BaseSetCFG != null)
                    return BaseSetCFG.getCFGValue<string>(Segment, "DBName", "");
                return "";
            }
            set { BaseSetCFG?.setCFGValue(Segment, "DBName", value); }
        }

        [DisplayName("连接端口")]
        public int DBPort
        {
            get
            {
                if (BaseSetCFG != null)
                    return BaseSetCFG.getCFGValue<int>(Segment, "DBPort", 3306);
                return 3306;
            }
            set { BaseSetCFG?.setCFGValue(Segment, "DBPort", value); }
        }

        [DisplayName("SQLSer连接字符串")]
        public string ConnectStr
        {
            get
            {
                string sMask = "data source={0};database={1};user id={2};password={3};pooling=false;charset=utf8;port={4};SslMode=none";
                return string.Format(sMask,
                    Address, DBName, DBUser, DBPass, DBPort.ToString());
            }
        }
    }

    /// <summary>
    /// 使用指定JSON文件的配置信息，可读写
    /// </summary>
    public class MySQLConfigS : JSONINISettings, IFSQLConfig
    {
        public string Segment { get; private set; } = "";
        public MySQLConfigS(string DestFileName,string DestSegment=""):base(DestFileName)
        {
            if (string.IsNullOrEmpty(DestSegment))
                Segment = "MySQL";
            else
                Segment = DestSegment;
        }

        [DisplayName("服务地址")]
        public string Address
        {
            get { return getCFGValue<string>(Segment, "Address", "127.0.0.1"); }
            set { setCFGValue(Segment, "Address", value); }
        }


        [DisplayName("登陆用户")]
        public string DBUser
        {
            get { return getCFGValue<string>(Segment, "DBUser", ""); }
            set { setCFGValue(Segment, "DBUser", value); }
        }

        [DisplayName("登陆密码")]
        public string DBPass
        {
            get { return getCFGValue<string>(Segment, "DBPass", ""); }
            set { setCFGValue(Segment, "DBPass", value); }
        }

        [DisplayName("连接初始数据库名称")]
        public string DBName
        {
            get { return getCFGValue<string>(Segment, "DBName", ""); }
            set { setCFGValue(Segment, "DBName", value); }
        }

        [DisplayName("连接端口")]
        public int DBPort
        {
            get { return getCFGValue<int>(Segment, "DBPort", 3306); }
            set { setCFGValue(Segment, "DBPort", value); }
        }

        [DisplayName("SQLSer连接字符串")]
        public string ConnectStr
        {
            get
            {
                string sMask = "data source={0};database={1};user id={2};password={3};pooling=false;charset=utf8;port={4};SslMode=none";
                return string.Format(sMask,
                    Address, DBName, DBUser, DBPass, DBPort.ToString());
            }
        }
    }

    /// <summary>
    /// 使用系统Configuration的只读信息
    /// </summary>
    public class MySQLConfigWebCFG: IFSQLConfig
    {
        private NameValueCollection CFGSection { get; set; } = null;
        [DisplayName("服务地址")]
        public string Address
        {
            get
            {
                string sTmp = CFGSection?["Address"];
                return (sTmp != null) ? sTmp : "127.0.0.1";
            }
        }
        [DisplayName("登陆用户")]
        public string DBUser
        {
            get
            {
                string sTmp = CFGSection?["DBUser"]; ;
                return (sTmp != null) ? sTmp : "";
            }
        }
        [DisplayName("登陆密码")]
        public string DBPass
        {
            get
            {
                string sTmp = CFGSection?["DBPass"]; ;
                return (sTmp != null) ? sTmp : "";
            }
        }
        [DisplayName("连接初始数据库名称")]
        public string DBName
        {
            get
            {
                string sTmp = CFGSection?["DBName"]; 
                return (sTmp != null) ? sTmp : "";
            }
        }

        [DisplayName("连接端口")]
        public int DBPort
        {
            get
            {
                int sTmp = 3306;
                int.TryParse(CFGSection?["DBPort"] , out sTmp);
                return sTmp;
            }
        }

        [DisplayName("SQLSer连接字符串")]
        public string ConnectStr
        {
            get
            {
                if (string.IsNullOrEmpty(DBName))
                {
                    string sMask = "data source={0};user id={2};password={3};pooling=false;charset=utf8;port={4};SslMode=none";
                    return string.Format(sMask,
                        Address, DBUser, DBPass, DBPort.ToString());
                }
                else
                {
                    string sMask = "data source={0};database={1};user id={2};password={3};pooling=false;charset=utf8;port={4};SslMode=none";
                    return string.Format(sMask,
                        Address, DBName, DBUser, DBPass, DBPort.ToString());
                }
            }
        }

        public MySQLConfigWebCFG(NameValueCollection DestCFGSection)
        {
            CFGSection = DestCFGSection;
        }
    }

    public class MySQLConfigDict : IFSQLConfig
    {
        private Dictionary<string,object> CFGSection { get; set; } = null;
        [DisplayName("服务地址")]
        public string Address
        {
            get
            {
                string sTmp = CFGSection?["Address"].ToString();
                return (sTmp != null) ? sTmp : "127.0.0.1";
            }
            set
            {
                if (CFGSection!=null)
                {
                    if (CFGSection.ContainsKey("Address"))
                        CFGSection["Address"] = value;
                    else
                        CFGSection.Add("Address", value);
                }
            }
        }
        [DisplayName("登陆用户")]
        public string DBUser
        {
            get
            {
                string sTmp = CFGSection?["DBUser"].ToString();
                return (sTmp != null) ? sTmp : "";
            }
            set
            {
                if (CFGSection != null)
                {
                    if (CFGSection.ContainsKey("DBUser"))
                        CFGSection["DBUser"] = value;
                    else
                        CFGSection.Add("DBUser", value);
                }
            }
        }
        [DisplayName("登陆密码")]
        public string DBPass
        {
            get
            {
                string sTmp = CFGSection?["DBPass"].ToString();
                return (sTmp != null) ? sTmp : "";
            }
            set
            {
                if (CFGSection != null)
                {
                    if (CFGSection.ContainsKey("DBPass"))
                        CFGSection["DBPass"] = value;
                    else
                        CFGSection.Add("DBPass", value);
                }
            }
        }
        [DisplayName("连接初始数据库名称")]
        public string DBName
        {
            get
            {
                string sTmp = CFGSection?["DBName"].ToString();
                return (sTmp != null) ? sTmp : "";
            }
            set
            {
                if (CFGSection != null)
                {
                    if (CFGSection.ContainsKey("DBName"))
                        CFGSection["DBName"] = value;
                    else
                        CFGSection.Add("DBName", value);
                }
            }
        }

        [DisplayName("连接端口")]
        public int DBPort
        {
            get
            {
                int sTmp = 3306;
                int.TryParse(CFGSection?["DBPort"].ToString(), out sTmp);
                return sTmp;
            }
            set
            {
                if (CFGSection != null)
                {
                    if (CFGSection.ContainsKey("DBPort"))
                        CFGSection["DBPort"] = value.ToString();
                    else
                        CFGSection.Add("DBPort", value.ToString());
                }
            }
        }

        [DisplayName("SQLSer连接字符串")]
        public string ConnectStr
        {
            get
            {
                if (string.IsNullOrEmpty(DBName))
                {
                    string sMask = "data source={0};user id={2};password={3};pooling=false;charset=utf8;port={4};SslMode=none";
                    return string.Format(sMask,
                        Address, DBUser, DBPass, DBPort.ToString());
                }
                else
                {
                    string sMask = "data source={0};database={1};user id={2};password={3};pooling=false;charset=utf8;port={4};SslMode=none";
                    return string.Format(sMask,
                        Address, DBName, DBUser, DBPass, DBPort.ToString());
                }
            }
        }

        public string toJSONString()
        {
            if ((CFGSection != null)&&(CFGSection.Count>0))
            {
                return JsonConvert.SerializeObject(CFGSection);
            }
            return "";
        }

        public MySQLConfigDict(Dictionary<string, object> DestCFGSection)
        {
            CFGSection = DestCFGSection;
        }
    }

    public class MySQLConfigR : IFSQLConfig
    {
        private IConfiguration CFGSection { get; set; } = null;
        [DisplayName("服务地址")]
        public string Address
        {
            get
            {
                string sTmp = CFGSection?.GetSection("Address").Value;
                return (sTmp != null) ? sTmp : "127.0.0.1";
            }
        }
        [DisplayName("登陆用户")]
        public string DBUser
        {
            get
            {
                string sTmp = CFGSection?.GetSection("DBUser").Value;
                return (sTmp != null) ? sTmp : "";
            }
        }
        [DisplayName("登陆密码")]
        public string DBPass
        {
            get
            {
                string sTmp = CFGSection?.GetSection("DBPass").Value;
                return (sTmp != null) ? sTmp : "";
            }
        }
        [DisplayName("连接初始数据库名称")]
        public string DBName
        {
            get
            {
                string sTmp = CFGSection?.GetSection("DBName").Value;
                return (sTmp != null) ? sTmp : "";
            }
        }

        [DisplayName("连接端口")]
        public int DBPort
        {
            get
            {
                int sTmp = 3306;
                int.TryParse(CFGSection?.GetSection("DBPort").Value, out sTmp);
                return sTmp;
            }
        }

        [DisplayName("SQLSer连接字符串")]
        public string ConnectStr
        {
            get
            {
                string sMask = "data source={0};database={1};user id={2};password={3};pooling=false;charset=utf8;port={4};SslMode=none";
                return string.Format(sMask,
                    Address, DBName, DBUser, DBPass, DBPort.ToString());
            }
        }

        public MySQLConfigR(IConfiguration DestCFGSection)
        {
            CFGSection = DestCFGSection;
        }
    }

    public class MySQLHelper
    {
        /// <summary>
        /// 清理SQL参数中的特殊字符
        /// </summary>
        /// <param name="sValue"></param>
        /// <returns></returns>
        public static string clearSQLValue(string sValue)
        {
            if (sValue == null)
                sValue = "";
            return sValue.Replace("%", string.Empty).Replace("?", string.Empty).Replace("<", string.Empty);
        }
        public Recorder_Error SysErr { get; set; } = null;
        public IFSQLConfig Config { get; private set; } = null;
        public MySQLHelper(IFSQLConfig DestConfig)
        {
            Config = DestConfig;
        }
        public MySQLHelper(IFSQLConfig DestConfig, Recorder_Error DestSysErr)
        {
            Config = DestConfig;
            SysErr = DestSysErr;
        }

        public bool IsSerIPReady
        {
            get { return (Config != null) && BaseTools.PingTest(Config.Address); }
        }

        #region SQL语句执行
        /// <summary>
        /// 执行SQL查询Select语句，同时获得结果数量
        /// 注意：应为自行前会检查SQL语句中是否，该函数暂时不支持存储过程
        /// </summary>
        /// <param name="DestSql">需要执行的目标SQL，必须包括Select</param>
        /// <param name="recCount">返回结果的数量</param>
        /// <param name="PageIdx">查询返回数据的起始页，从1开始</param>
        /// <param name="PageCount">查询返回数据每页记录数</param>
        /// <returns></returns>
        public DataTable runSelect(string DestSql, out int recCount,
            int PageIdx=0,int PageCount=0)
        {
            recCount = 0;
            DataSet tDataset = new DataSet();
            DataTable tDTable = null;
            if ((!string.IsNullOrEmpty(DestSql)) && (Config != null)
                && ((DestSql.ToLower().IndexOf("select") >= 0)      // 处理select查询语句
                || (DestSql.ToLower().IndexOf("call") >= 0)))       // 处理call存储过程
                try
                {
                    using (MySqlConnection tDBCon = new MySqlConnection(Config.ConnectStr))
                        try
                        {
                            tDBCon.Open();
                            MySqlDataAdapter tAdapter = new MySqlDataAdapter();
                            tAdapter.SelectCommand = new MySqlCommand(DestSql, tDBCon);
                            string sResultTBName = "SelectResult";
                            if (PageCount > 0)
                            {
                                if (PageIdx <= 0)
                                    PageIdx = 1;
                                // srcTable:导入DataSet中的表名称，可以是任何名字，但不能为空
                                tAdapter.Fill(tDataset, (PageIdx - 1) * PageCount, PageCount, sResultTBName);
                            }
                            else
                            {
                                tAdapter.Fill(tDataset, sResultTBName);
                            }
                            tDTable = tDataset.Tables[sResultTBName];
                            recCount = tDTable.Rows.Count;
                        }
                        finally
                        {
                            tDBCon.Close();
                        }
                }
                catch (Exception ex)
                {
                    if (SysErr != null)
                        SysErr.setRecord(ex);
                    else
                        throw;
                }
            return tDTable;
        }

        /// <summary>
        /// 执行查询，并返回由查询返回的结果集中的第一行的第一列。 其他列或行将被忽略。
        /// </summary>
        /// <param name="DestSql"></param>
        /// <param name="DBName"></param>
        /// <returns>结果集中第一行的第一列或 null 引用（如果结果集为空）</returns>
        public object runSelect(string DestSql)
        {
            if ((!string.IsNullOrEmpty(DestSql)) && (Config != null)
                && (DestSql.ToLower().IndexOf("select") >= 0))
                try
                {
                    using (MySqlConnection tDBCon = new MySqlConnection(Config.ConnectStr))
                        try
                        {
                            tDBCon.Open();
                            MySqlCommand tDBCommand = new MySqlCommand(DestSql, tDBCon);
                            return tDBCommand.ExecuteScalar();
                        }
                        finally
                        {
                            tDBCon.Close();
                        }
                }
                catch (Exception ex)
                {
                    if (SysErr != null)
                        SysErr.setRecord(ex);
                    else
                        throw;
                }
            return "";
        }

        // <summary>
        /// Update操作数据
        /// </summary>
        /// <param name="updateSQL">等待执行的Update语句</param>
        /// <param name="DBName">目标数据库名称</param>
        /// <returns>返回影响的记录数量</returns>
        public int updateValues(string updateSQL)
        {
            if ((!string.IsNullOrEmpty(updateSQL)) && (Config != null)
                && ((updateSQL.ToLower().IndexOf("update") >= 0) || (updateSQL.ToLower().IndexOf("insert") >= 0)))

                try
                {
                    using (MySqlConnection tDBCon = new MySqlConnection(Config.ConnectStr))
                        try
                        {
                            tDBCon.Open();
                            MySqlCommand tCommand = new MySqlCommand(updateSQL, tDBCon);
                            return tCommand.ExecuteNonQuery();
                        }
                        finally
                        {
                            tDBCon.Close();
                        }
                }
                catch (Exception ex)
                {
                    if (SysErr != null)
                        SysErr.setRecord(ex);
                    else
                        throw;
                }
            return -1;
        }

        /// <summary>
        /// 带参数方式写入字段
        /// </summary>
        /// <param name="updateSQL"></param>
        /// <param name="sParameterName">参数名称，不包括@</param>
        /// <param name="sParamaterValue"></param>
        /// <returns></returns>
        public int updateValues(string updateSQL, string sParameterName, string sParamaterValue)
        {
            if ((!string.IsNullOrEmpty(updateSQL)) && (Config != null)
                && (updateSQL.ToLower().IndexOf("update") >= 0) //  || (updateSQL.ToLower().IndexOf("insert") >= 0)
                && (updateSQL.IndexOf("@" + sParameterName) >= 0))
                try
                {
                    using (MySqlConnection tDBCon = new MySqlConnection(Config.ConnectStr))
                        try
                        {
                            tDBCon.Open();
                            MySqlCommand tCommand = new MySqlCommand(updateSQL, tDBCon);
                            tCommand.Parameters.AddWithValue("@" + sParameterName, sParamaterValue);
                            return tCommand.ExecuteNonQuery();
                        }
                        finally
                        {
                            tDBCon.Close();
                        }
                }
                catch (Exception ex)
                {
                    if (SysErr != null)
                        SysErr.setRecord(ex);
                    else
                        throw;
                }
            return -1;
        }

        #endregion
    }
}
